import java.io.BufferedInputStream;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.*;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.CellRangeAddress8Bit;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.eventusermodel.XSSFReader;

/**
 * Created by yan_my on 2016/10/30.
 */
public class excel {
    public static void main(String[] args)throws IOException{
        InputStream in = new BufferedInputStream(new FileInputStream("input/excel.xls"));
        HSSFWorkbook book = new HSSFWorkbook(in);
        HSSFSheet sheet=book.getSheetAt(0);
        HSSFRow row=sheet.getRow(1);
        System.out.println("读取第一条信息:");
        System.out.println(row.getCell(0));
        System.out.println(row.getCell(1));
        FileOutputStream out =new FileOutputStream("output/newExcel.xls");

        HSSFCellStyle style=book.createCellStyle();
        style.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00"));
        style.setAlignment(HorizontalAlignment.CENTER);
        for(int i=0;i<4;i++){
            row=sheet.getRow(i);
            for(int j=0;j<4;j++) {
                row.getCell(j).setCellStyle(style);
            }
        }

        //合并单元格
        HSSFRow aRow=sheet.getRow(0);
        HSSFCell mergedCell=aRow.createCell(4);
        mergedCell.setCellValue("合并");
        CellRangeAddress region=new CellRangeAddress(0,5,3,3);
        sheet.addMergedRegion(region);

        //单元格计算
        row=sheet.createRow(4);
        HSSFCell cell=row.createCell(4);
        cell.setCellValue("花费");
        cell=row.createCell(4);
        cell.setCellFormula("D2+D3+D4");

        book.write(out);
        in.close();
        out.close();

    }
}
